package com.iot.app.springboot.dashboard;

import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

import org.springframework.beans.factory.annotation.Autowired;
//import org.springframework.data.hadoop.hbase.HbaseTemplate;
import org.springframework.messaging.simp.SimpMessagingTemplate;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Controller;

import com.iot.app.springboot.dao.entity.POITrafficData;
import com.iot.app.springboot.dao.entity.TotalTrafficData;
import com.iot.app.springboot.dao.entity.WindowTrafficData;
import com.iot.app.springboot.vo.Response;
import org.springframework.stereotype.Service;

/**
 * Service class to send traffic data messages to dashboard ui at fixed interval using web-socket.
 * 
 * @author abaghel
 *
 */

@Service
//@Controller
public class TrafficDataService {

    private static Connection connection;
    private static Statement statement;
    private ResultSet rs;
    private static final String url  = "jdbc:phoenix:node01:2181";

    @Autowired
    private SimpMessagingTemplate template;

    private static DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

    public TrafficDataService() throws SQLException {
    }

    //Method sends traffic data message in every 5 seconds.
    @Scheduled(fixedRate = 5000)
    public void trigger() throws SQLException {
        List<TotalTrafficData> totalTrafficList = new ArrayList<TotalTrafficData>();
        List<WindowTrafficData> windowTrafficList = new ArrayList<WindowTrafficData>();
        List<POITrafficData> poiTrafficList = new ArrayList<POITrafficData>();

        //定义查询的sql语句，注意大小写
        //String sql = "select * from \"total_traffic_data\" where \"recordDate\" = \'2020-10-05\'";
        //System.out.println(sql);
        //执行sql语句
        try {
            connection = DriverManager.getConnection(url);
            PreparedStatement pstmt = connection.prepareStatement("select * from \"total_traffic_data\" where \"recordDate\" = ?");
            pstmt.setString(1, new SimpleDateFormat("yyyy-MM-dd").format(new Date()));
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
//                System.out.println("totalCount:" + rs.getString("totalCount"));
//                System.out.println("vehicleType:" + rs.getString("vehicleType"));
//                System.out.println("routeId:" + rs.getString("routeId"));
//                System.out.println("recordDate:" + rs.getString("recordDate"));
//                System.out.println("-------------------------");
                TotalTrafficData total = new TotalTrafficData();
                total.setRecordDate(rs.getString("recordDate"));
                total.setRouteId(rs.getString("routeId"));
                total.setVehicleType(rs.getString("vehicleType"));
                total.setTotalCount(Long.valueOf(rs.getString("totalCount")));
                totalTrafficList.add(total);
            }

            pstmt = connection.prepareStatement("select * from \"window_traffic_data\" where \"recordDate\" = ?");
            pstmt.setString(1, new SimpleDateFormat("yyyy-MM-dd").format(new Date()));
            rs = pstmt.executeQuery();
            while (rs.next()) {
//                System.out.println("windowsCount:" + rs.getString("totalCount"));
//                System.out.println("vehicleType:" + rs.getString("vehicleType"));
//                System.out.println("routeId:" + rs.getString("routeId"));
//                System.out.println("recordDate:" + rs.getString("recordDate"));
//                System.out.println("-------------------------");
                WindowTrafficData windows = new WindowTrafficData();
                windows.setRecordDate(rs.getString("recordDate"));
                windows.setRouteId(rs.getString("routeId"));
                windows.setVehicleType(rs.getString("vehicleType"));
                windows.setTotalCount(Long.valueOf(rs.getString("totalCount")));
                windowTrafficList.add(windows);
            }

            String sql = "select * from \"poi_traffic_data\"";
            statement = connection.createStatement();
            rs = statement.executeQuery(sql);
            while (rs.next()) {
//                System.out.println("vehicleId:" + rs.getString("vehicleId"));
//                System.out.println("vehicleType:" + rs.getString("vehicleType"));
//                System.out.println("distance:" + rs.getString("distance"));
//                System.out.println("-------------------------");
                POITrafficData poi = new POITrafficData();
                poi.setDistance(Double.valueOf(rs.getString("distance")));
                poi.setVehicleId(rs.getString("vehicleId"));
                poi.setVehicleType(rs.getString("vehicleType"));
                poiTrafficList.add(poi);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                connection.close();
            }
        }

        //prepare response
        Response response = new Response();
        response.setTotalTraffic(totalTrafficList);
        response.setWindowTraffic(windowTrafficList);
        response.setPoiTraffic(poiTrafficList);
        //logger.info("Sending to UI "+response);
        //send to ui
        this.template.convertAndSend("/topic/trafficData", response);
    }
}
